package dao;

import domain.School;
import domain.University;
import service.UniversityService;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class SchoolDao {
    private static SchoolDao schoolDao = new SchoolDao();
    private static Collection<School> schools;
    public static SchoolDao getInstance() {
        return schoolDao;
    }
    public int count(University university) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        int id = university.getId();
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(id) as cnt_by_id FROM school WHERE university_id= ?");
        preparedStatement.setInt(1,id);
        int count_by_id = 0;
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            count_by_id=resultSet.getInt("cnt_by_id");
        }
        JdbcHelper.close(preparedStatement, connection);
        return count_by_id;
    }
    public  boolean add(School school, Connection connection) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO School"+"(id,description,no )"+"VALUES (?,?,?)");
        preparedStatement.setInt(1,school.getId());
        preparedStatement.setString(2,school.getDescription());
        preparedStatement.setString(3,school.getNo());
        int rowAffected=preparedStatement.executeUpdate();
        return rowAffected>0;
    }
    public boolean delete(int id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM school " +
                " WHERE id=?");
        System.out.println(id);
        preparedStatement.setInt(1, id);
        int affectedRowNum = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement, connection);
        return affectedRowNum > 0;
    }    public School update(School school) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateSchool_sql1 = "update school set  description = ? , no = ? where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateSchool_sql1);
        stmt_update_name.setString(1,school.getDescription());
        stmt_update_name.setString(2,school.getNo());
        stmt_update_name.setInt(3,school.getUniversity().getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }



    public Collection<School> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<School> desiredSchools = new HashSet<School>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM school ");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"School");
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet_select = pstmt_select.executeQuery();
        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String description = resultSet_select.getString("description");
            String no = resultSet_select.getString("no");
            String remarks = resultSet_select.getString("remarks");
            School desiredSchool = new School(id, description,no,remarks);
            desiredSchools.add(desiredSchool);
        }
        JdbcHelper.close(resultSet_select, pstmt_select);
        return desiredSchools;
    }
    public School find(int id, Connection connection) throws SQLException {
        School desiredSchool = null;

        String sql_select_id = "SELECT * FROM school WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet = pstmt_select_id.executeQuery();
        if (resultSet.next()){
            int id2 = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int university_id = resultSet.getInt("university_id");
            University university = UniversityService.getInstance().find(university_id);
            desiredSchool = new School(id2,description,no,remarks,university);
        }
        JdbcHelper.close(resultSet,pstmt_select_id,connection);
        return desiredSchool;
    }

}

